﻿
-- ================================================
-- Remarks: 
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Dasideen_ContentStatistics_GetContentMetaDataMasterWithContentStaticstics')
	BEGIN
		DROP  Procedure  Dasideen_ContentStatistics_GetContentMetaDataMasterWithContentStaticstics
	END

GO

CREATE PROCEDURE [Dasideen_ContentStatistics_GetContentMetaDataMasterWithContentStaticstics]
-- =============================================
-- Author:		<Sagar>
-- Create date: <21-04-2015>
-- Description:	<Dasideen_ContentStatistics_GetContentMetaDataMasterWithContentStaticstics>
-- =============================================
-- Add the parameters for the stored procedure here
	(
		@ContentKey INT 
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT OFF;

	--SELECT		[ContentMetaDataMaster].[ContentKey],
	--			[ContentMetaDataMaster].[ContentCode], 
	--			[ContentMetaDataMaster].[ContentName], 
	--			[ContentMetaDataMaster].[ContentDescription], 
	--			[ContentMetaDataMaster].[ContentTypeKey], 
	--			[ContentMetaDataMaster].[ContentTotalDownloads], 
	--			[ContentMetaDataMaster].[ContentTotalLikes], 
	--			[ContentMetaDataMaster].[ContentTotalViews], 
	--			[ContentMetaDataMaster].[Sys_DateOfCreation], 
	--			[ContentMetaDataMaster].[Sys_CreatedByID], 
	--			AVG([ContentRating].[ContentRatingValue]) AS [AvrageRating], 
	--			COUNT([ContentRating].[ContentRatingKey]) AS [TotalVotes], 
	--			[User].[UserLoginID], 
	--			[User].[UserFirstName], 
	--			[User].[UserLastName]
 --   FROM		[User] RIGHT OUTER JOIN [ContentMetaDataMaster] 
	--ON			[User].[UserKey] = [ContentMetaDataMaster].[Sys_CreatedByID] LEFT OUTER JOIN [ContentRating] 
	--ON			[ContentMetaDataMaster].[ContentKey] = [ContentRating].[ContentRatingContentKey]
 --   GROUP BY	[ContentMetaDataMaster].[ContentKey], 
	--			[ContentMetaDataMaster].[ContentCode], 
	--			[ContentMetaDataMaster].[ContentName], 
	--			[ContentMetaDataMaster].[ContentDescription], 
	--			[ContentMetaDataMaster].[ContentTypeKey], 
	--			[ContentMetaDataMaster].[ContentTotalDownloads], 
	--			[ContentMetaDataMaster].[ContentTotalLikes], 
	--			[ContentMetaDataMaster].[ContentTotalViews], 
	--			[ContentMetaDataMaster].[Sys_DateOfCreation], 
	--			[ContentMetaDataMaster].[Sys_CreatedByID], 
	--			[User].[UserLoginID], 
	--			[User].[UserFirstName], 
	--			[User].[UserLastName]
 --   HAVING		(
	--				([ContentMetaDataMaster].[ContentKey] = @ContentKey)
	--			)

	SELECT     
				[ContentMetaDataMaster].[ContentKey], 
				[ContentMetaDataMaster].[ContentCode], 
				[ContentMetaDataMaster].[ContentName], 
				[ContentMetaDataMaster].[ContentMetaTagKeyword], 
				[ContentMetaDataMaster].[ContentMetaTagDescription],
				[ContentMetaDataMaster].[ContentHtmlData], 
                [ContentMetaDataMaster].[ContentTypeKey], 
				[ContentTypeMaster].[ContentTypeCode], 
				[ContentTypeMaster].[ContentTypeName], 
				[ContentMetaDataMaster].[ContentTotalDownloads],
				[ContentMetaDataMaster].[ContentTotalLikes], 
				[ContentMetaDataMaster].[ContentTotalViews], 
				AVG([ContentRating].[ContentRatingValue]) AS [AvrageRating], 
				COUNT([ContentRating].[ContentRatingKey]) AS [TotalVotes], 
				[ContentMetaDataMaster].[Sys_DateOfCreation], 
				[ContentMetaDataMaster].[Sys_CreatedByID], 
				[User].[UserLoginID], 
				[User].[UserFirstName], 
				[User].[UserLastName] 
	FROM         
				[ContentTypeMaster] RIGHT OUTER JOIN [ContentMetaDataMaster] 
	ON			[ContentTypeMaster].[ContentTypeKey] = [ContentMetaDataMaster].[ContentTypeKey] LEFT OUTER JOIN [User] 
	ON			[ContentMetaDataMaster].[Sys_CreatedByID] = [User].[UserKey] LEFT OUTER JOIN [ContentRating] 
	ON			[ContentMetaDataMaster].[ContentKey] = [ContentRating].[ContentRatingContentKey] 
	GROUP BY 
				[ContentMetaDataMaster].[ContentKey], 
				[ContentMetaDataMaster].[ContentCode], 
				[ContentMetaDataMaster].[ContentName], 
				[ContentMetaDataMaster].[ContentMetaTagKeyword], 
				[ContentMetaDataMaster].[ContentMetaTagDescription],
				[ContentMetaDataMaster].[ContentHtmlData], 
				[ContentMetaDataMaster].[ContentTypeKey], 
				[ContentMetaDataMaster].[ContentTotalDownloads], 
				[ContentMetaDataMaster].[ContentTotalLikes], 
				[ContentMetaDataMaster].[ContentTotalViews], 
				[ContentMetaDataMaster].[Sys_DateOfCreation], 
				[ContentMetaDataMaster].[Sys_CreatedByID], 
				[User].[UserLoginID], 
				[User].[UserFirstName], 
				[User].[UserLastName], 
				[ContentTypeMaster].[ContentTypeCode], 
				[ContentTypeMaster].[ContentTypeName] 
	HAVING      
				(
					(ContentMetaDataMaster.ContentKey = @ContentKey)
				)

END
GO
